package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import bookshop.dao.BookDao;
import bookshop.dao.CustCommentDao;
import bookshop.dao.CustomerDao;
import bookshop.dao.DaoFactory;
import bookshop.dao.OrderDao;
import bookshop.domain.CustComment;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.BookQueryHelper;
import bookshop.service.helper.CustCommentQueryHelper;
import bookshop.utils.DBUtils;

public class CustCommentDaoJDBCImpl implements CustCommentDao {

	
    private static final String ADD_CUST_COMMENT="insert into cust_comment(order_id,book_code,cust_ip,title,content,cust_id,mark,issue_time) values(?,?,?,?,?,?,?,?)";
    private static final String COUNT_COMMENT_BY_CUSTID = "select count(*) as total from cust_comment where cust_id=?"; 
    private static final String AVG_MARK_BY_BOOKCODE = "select avg(mark) as avgmark from cust_comment where book_code=?";
    private static final String DEL_COMMENT_BY_ID="delete from cust_comment where id=?";
    private static final String GET_COMMENT_BYID="select * from cust_comment where id=?";
    private static final String GET_COMMENT_BYCUSTID="select * from cust_comment where cust_id=?";
    
    private String generateSQL(CustCommentQueryHelper helper) {
		
		String sql = "select * from cust_comment where 1=1 ";
		
	    if(helper.getCustId()!=null){
	    	sql += " and cust_id = '" + helper.getCustId()+"' ";
	    }
	    
	    if(helper.getOrderId()!=null){
	    	sql += " and order_id = '" + helper.getOrderId()+"' ";
	    }
	    
	    if(helper.getBookCode()!=null){
	    	sql += " and book_code = '" + helper.getBookCode()+"' ";
	    }
	    
	    
	    if(helper.getQryCommentYear()!=null){
			sql += " and date_format(issue_time,'%Y') = '" + helper.getQryCommentYear()+"'";
			
		}
		
		
		if(helper.getQryCommentMonth()!=null){
			sql += " and date_format(issue_time,'%m') = '" + helper.getQryCommentMonth()+"'";
			
		}
		
		if(helper.getQryCommentDay()!=null){
			sql += " and date_format(issue_time,'%d') = '" + helper.getQryCommentDay()+"'";
			
		}
        
		
		sql += " order by issue_time";
		
		
		return sql;
		
	}
    
    public void AddCustCommet(CustComment comment){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {

			ps = ct.prepareStatement(ADD_CUST_COMMENT);
			ps.setString(1, comment.getOrderInfo().getOrderId());
			ps.setString(2, comment.getBook().getBookCode());
			ps.setString(3, comment.getCustIp());
			ps.setString(4, comment.getTitle());
			ps.setString(5, comment.getContent());
			ps.setString(6, comment.getCustomer().getCustomerId());
			ps.setInt(7, comment.getMark());
			ps.setTimestamp(8, new java.sql.Timestamp(comment.getIssueTime().getTime()));
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书信息保存失败，可能ISBN编号出现重复，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
    	
    }

    public Long countCommentByCustId(String custId){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long all = 0L;
		try {
			ps = ct.prepareStatement(COUNT_COMMENT_BY_CUSTID);
			ps.setString(1, custId);
			rs = ps.executeQuery();
			if (rs.next()) {
				all = rs.getLong("total");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
    	
    	return all;
    }

    public Integer avgMarkByBookCode(String bookCode){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer avg = 0;
		try {
			ps = ct.prepareStatement(AVG_MARK_BY_BOOKCODE);
			ps.setString(1, bookCode);
			rs = ps.executeQuery();
			if (rs.next()) {
				avg = rs.getInt(1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
    	
    	
    	return avg;
    }
    
    public Long countCommentsByCondition(CustCommentQueryHelper helper) {
		String sql = this.generateSQL(helper);

		sql = sql.replace("*", "count(*) as total");
		sql = sql.substring(0, sql.indexOf("order by"));

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return count;
	}
    
    public List<CustComment> getScopedComments(CustCommentQueryHelper helper, int startIndex,
			int fetchSize) {

		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;

		System.out.println(sql);
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<CustComment> comments = new ArrayList<CustComment>();

		try {
			ps = ct.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {

				CustComment comment = new CustComment();
				comment.setCommentId(rs.getInt(1));
				OrderDao orderDao = (OrderDao)DaoFactory.getDao("orderDao");
				comment.setOrderInfo(orderDao.GetOrderByOrderId(rs.getString(2)));
				BookDao bookDao = (BookDao)DaoFactory.getDao("bookDao");
				comment.setBook(bookDao.getBookByCode(rs.getString(3)));
				comment.setCustIp(rs.getString(4));
				comment.setTitle(rs.getString(5));
				comment.setContent(rs.getString(6));
				CustomerDao customerDao = (CustomerDao)DaoFactory.getDao("customerDao");
				comment.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				comment.setMark(rs.getInt(8));
				comment.setIssueTime(new java.sql.Date(rs.getTimestamp(9).getTime()));

				comments.add(comment);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书评论列表获取失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return comments;

	}

	public List<CustComment> getCustCommentByCondition(
			CustCommentQueryHelper helper) {
		
		String sql = this.generateSQL(helper);
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<CustComment> comments = new ArrayList<CustComment>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {

				CustComment comment = new CustComment();
				comment.setCommentId(rs.getInt(1));
				OrderDao orderDao = (OrderDao)DaoFactory.getDao("orderDao");
				comment.setOrderInfo(orderDao.GetOrderByOrderId(rs.getString(2)));
				BookDao bookDao = (BookDao)DaoFactory.getDao("bookDao");
				comment.setBook(bookDao.getBookByCode(rs.getString(3)));
				comment.setCustIp(rs.getString(4));
				comment.setTitle(rs.getString(5));
				comment.setContent(rs.getString(6));
				CustomerDao customerDao = (CustomerDao)DaoFactory.getDao("customerDao");
				comment.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				comment.setMark(rs.getInt(8));
				comment.setIssueTime(new java.sql.Date(rs.getTimestamp(9).getTime()));

				comments.add(comment);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书评论列表获取失败!");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return comments;
	}

    public void delCustCommentById(Integer id){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		try {
			ps = ct.prepareStatement(DEL_COMMENT_BY_ID);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
    }

    public CustComment getCustCommentById(Integer id){
    	Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		CustComment comment=null;

		try {
			ps = ct.prepareStatement(GET_COMMENT_BYID);
			ps.setInt(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {

				comment = new CustComment();
				comment.setCommentId(rs.getInt(1));
				OrderDao orderDao = (OrderDao)DaoFactory.getDao("orderDao");
				comment.setOrderInfo(orderDao.GetOrderByOrderId(rs.getString(2)));
				BookDao bookDao = (BookDao)DaoFactory.getDao("bookDao");
				comment.setBook(bookDao.getBookByCode(rs.getString(3)));
				comment.setCustIp(rs.getString(4));
				comment.setTitle(rs.getString(5));
				comment.setContent(rs.getString(6));
				CustomerDao customerDao = (CustomerDao)DaoFactory.getDao("customerDao");
				comment.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				comment.setMark(rs.getInt(8));
				comment.setIssueTime(new java.sql.Date(rs.getTimestamp(9).getTime()));


			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书评论列表获取失败!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return comment;
    }

	
	public List<CustComment> getCustCommentByCustId(CustCommentQueryHelper helper) {
		
		String sql = this.generateSQL(helper);
	
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		CustComment comment=null;
		
		List<CustComment> comments = new ArrayList<CustComment>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {

				comment = new CustComment();
				comment.setCommentId(rs.getInt(1));
				OrderDao orderDao = (OrderDao)DaoFactory.getDao("orderDao");
				comment.setOrderInfo(orderDao.GetOrderByOrderId(rs.getString(2)));
				BookDao bookDao = (BookDao)DaoFactory.getDao("bookDao");
				comment.setBook(bookDao.getBookByCode(rs.getString(3)));
				comment.setCustIp(rs.getString(4));
				comment.setTitle(rs.getString(5));
				comment.setContent(rs.getString(6));
				CustomerDao customerDao = (CustomerDao)DaoFactory.getDao("customerDao");
				comment.setCustomer(customerDao.getCustomerById(rs.getString(7)));
				comment.setMark(rs.getInt(8));
				comment.setIssueTime(new java.sql.Date(rs.getTimestamp(9).getTime()));

				comments.add(comment);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("个人图书评论列表获取失败!");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return comments;
	
	}
}
